package util;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 数据库操作基础类
 *
 * @param <T> 继承该类之后子类就具有查询数据库功能，并且有该类方法
 */
public class BaseDao<T> {
    private Class<T> clazz; //实体类Class 对象
    private List<String> fields;// 数据库中字段名
    private String tableName;// 数据库表名

    public BaseDao() {
        //通过泛型获取实体类
        Type type = this.getClass().getGenericSuperclass();
        if (type instanceof ParameterizedType) {
            ParameterizedType parameterizedType = (ParameterizedType) type;
            Type[] types = parameterizedType.getActualTypeArguments();
            clazz = (Class<T>) types[0]; //
            try {
                T t = clazz.newInstance();
                Field[] declaredFields = clazz.getDeclaredFields(); // 获取实体类全部属性（包括私有）
                String name = clazz.getSimpleName();// 获取实体类名
                // 把实体类名转换成下划线的数据库表明 例：User -> user  UserName->user_name
                tableName = (name.charAt(0) + "").toLowerCase() + humpToLine(name.substring(1));
                //把实体类属性名转换成数据库中带下划线的字段名 例： UserName->user_name
                fields = Arrays.stream(declaredFields).map(f -> humpToLine(f.getName())).collect(Collectors.toList());
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }

    public int insert(T data) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultSet = 0;
        try {
            connection = DButils.getConnection();
            StringBuilder sb = new StringBuilder("INSERT INTO ");
            sb.append(tableName);
            sb.append("(");
            sb.append(String.join(",", fields));
            sb.append(")");
            sb.append(" VALUES(");
            fields.stream().forEach(f -> {
                sb.append("?,");
            });
            String sql = sb.substring(0, sb.length() - 1) + ")";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < fields.size(); i++) {
                String methodName = "get";
                String tem = lineToHump(fields.get(i));
                methodName += tem.substring(0, 1).toUpperCase() + tem.substring(1);
                Object value = clazz.getMethod(methodName).invoke(data);
//                System.out.print(value + ",");
                preparedStatement.setObject(i + 1, value);
            }
//            System.out.println();
            // 4. 执行查询sql
            resultSet = preparedStatement.executeUpdate();
            System.out.println(getSql(preparedStatement));

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            DButils.close(connection, preparedStatement);
            return resultSet;
        }
    }

    /**
     * 根据id删除一条数据
     *
     * @param id
     * @return
     */
    public int delete(Serializable id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultSet = 0;
        try {
            connection = DButils.getConnection();
            StringBuilder sb = new StringBuilder("DELETE FROM ");
            sb.append(tableName + " WHERE id=?");
            String sql = sb.toString();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            resultSet = preparedStatement.executeUpdate();
            System.out.println(getSql(preparedStatement));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(connection, preparedStatement);
            return resultSet;
        }
    }

    /**
     * 根据id修改
     *
     * @param data
     * @return
     */
    public int update(T data) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        List<Object> params = new LinkedList<>(); // 参数列表
        Object id = null; // id 参数
        int resultSet = 0; // 返回值 1 成功 0 失败
        try {
            connection = DButils.getConnection();
            StringBuilder sb = new StringBuilder("UPDATE ");
            sb.append(tableName + " SET ");
            for (int i = 0; i < fields.size(); i++) {
                String methodName = "get";
                String tem = lineToHump(fields.get(i));
                methodName += tem.substring(0, 1).toUpperCase() + tem.substring(1);
                Object value = clazz.getMethod(methodName).invoke(data);
                //如果传递的参数不为空则设置新值
                if (null != value && !"".equals(value)) {
                    // 把主键id 排除并获取id值 作为后续id参数
                    if (tem.equals("id")) {
                        id = value;
                    } else {
                        sb.append(fields.get(i));
                        sb.append("=");
                        sb.append("?,");
                        params.add(value);
                    }

                }
            }
            // 去除最后多余的逗号
            String sql = sb.substring(0, sb.length() - 1);
            sql += " WHERE id=?";
            preparedStatement = connection.prepareStatement(sql);
            int i;
            // 设置更新值参数
            for (i = 0; i < params.size(); i++) { // 根据参数列表给sql 赋值参数
                preparedStatement.setObject(i + 1, params.get(i));
            }
            // 设置id参数
            preparedStatement.setObject(i + 1, id);
            resultSet = preparedStatement.executeUpdate();
            System.out.println(getSql(preparedStatement));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(connection, preparedStatement); // 关闭数据库连接 和 PreparedStatement
            return resultSet;
        }
    }

    /**
     * 根据id获取实体
     *
     * @param id
     * @return
     */
    public T getOne(Serializable id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        T data = null;
        try {
            connection = DButils.getConnection();
            StringBuilder sb = new StringBuilder("SELECT  ");
            sb.append(String.join(",", fields));
            sb.append(" FROM " + tableName);
            sb.append(" WHERE id=?");
            String sql = sb.toString();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            System.out.println(getSql(preparedStatement));
            if (resultSet.next()) {
                data = (T) setField(fields,clazz,resultSet);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(connection, preparedStatement);
            return data;
        }
    }

    // 根据字段获取
    public T getOne(String column, Object value) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        T data = null;
        try {
            connection = DButils.getConnection();
            StringBuilder sb = new StringBuilder("SELECT  ");
            sb.append(String.join(",", fields));
            sb.append(" FROM " + tableName);
            sb.append(" WHERE " + column + "=?");
            String sql = sb.toString();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, value);
            ResultSet resultSet = preparedStatement.executeQuery();
            System.out.println(getSql(preparedStatement));
            if (resultSet.next()) {
                data = (T)setField(fields,clazz,resultSet);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButils.close(connection, preparedStatement);
            return data;
        }
    }
    public static String getTableName(Class clazz) {
        String name = clazz.getSimpleName();// 获取实体类名
        // 把实体类名转换成下划线的数据库表明 例：User -> user  UserName->user_name
        String tableName = (name.charAt(0) + "").toLowerCase() + humpToLine(name.substring(1));
        return tableName;
    }
    public static List<String> getFields(Class clazz) {
        Field[] declaredFields = clazz.getDeclaredFields(); // 获取实体类全部属性（包括私有）
        // 把实体类名转换成下划线的数据库表明 例：User -> user  UserName->user_name
        //把实体类属性名转换成数据库中带下划线的字段名 例： UserName->user_name
        List<String> fields = Arrays.stream(declaredFields).map(f -> humpToLine(f.getName())).collect(Collectors.toList());
        return fields;
    }
    /**
     * 从sql 结果集获取值并赋值
     * @param fields 类属性名 List
     * @param clas 类 class
     * @param resultSet sql 结果集
     * @return
     * @throws NoSuchFieldException
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public static Object setField( List<String> fields,Class<?> clas,ResultSet resultSet) throws NoSuchFieldException, SQLException, IllegalAccessException, InstantiationException {
        Object data = clas.newInstance();
        int index = 0;
        for (; index < fields.size(); index++) {
            Field declaredField = clas.getDeclaredField(lineToHump(fields.get(index)));
            declaredField.setAccessible(true);
            String simpleName = declaredField.getType().getSimpleName();
            Object object = null;
            switch (simpleName.toLowerCase()) {
                case "string":
                    object = resultSet.getString(fields.get(index));
                    break;
                case "integer":
                case "int":
                    object = resultSet.getInt(fields.get(index));
                    break;
                default:
                    object = resultSet.getObject(fields.get(index));
            }
            declaredField.set(data, object);
        }
        return data;
    }
    //获取编译后sql
    public static String getSql(PreparedStatement preparedStatement ) {
        String s = preparedStatement.toString();
       return s.substring(s.lastIndexOf(":") + 1);
    }
    /**
     * 下划线转驼峰
     */
    public static String lineToHump(String str) {
        Pattern linePattern = Pattern.compile("_(\\w)");
        str = str.toLowerCase();
        Matcher matcher = linePattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    /**
     * 驼峰转下划线
     */
    public static String humpToLine(String str) {
        Pattern humpPattern = Pattern.compile("[A-Z]");
        Matcher matcher = humpPattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }
}
